Database privileged role assignments should be restricted to IAO-authorized DBMS accounts.

Overview

Finding ID	Version	Rule ID	IA Controls	Severity
V-15626	DG0116-SQLServer9	SV-24102r2_rule	ECLP-1	Medium

Description
Roles assigned privileges to perform DDL and/or system configuration actions in the database can lead to compromise of any data in the database as well as operation of the DBMS itself. Restrict assignment of privileged roles to authorized personnel and database accounts to help prevent unauthorized activity.

STIG	Date
Microsoft SQL Server 2005 Instance Security Technical Implementation Guide	2015-06-16

Details

Check Text ( C-23589r2_chk )
View SYSADMIN group membership: From the query prompt: SELECT p.name FROM [master].sys.server_principals p, [master].sys.server_role_members m WHERE p.principal_id = m.member_principal_id AND m.member_principal_id <> 1 AND m.role_principal_id = 3 ORDER BY p.name Verify with the DBA that all users listed under System Administrators are authorized DBAs and authorized to manage the database system audit configuration. Authorized application object owner accounts are Not a Finding unless they are not disabled (DG0004). If any authorized application object owner accounts are enabled, this is a Finding (for DG0116). If this is a production environment, verify with the DBA that none of the users listed under the SYSADMIN fixed server role are application administrators. If the BUILTIN/Administrators group is listed as a member of the SYSADMIN fixed server role, this is a Finding. Note: Removing BUILTIN/Administrators without creating an appropriate group to administer SQL Server will result in a ‘lock out’ condition within SQL Server. Ensure the proper steps have been taken to create a new group that is added to SYSADMIN fixed server role before removing BUILTIN/Administrators. Also, ensure the SA password is known before making this change.

Check Text ( C-23589r2_chk )

View SYSADMIN group membership:

From the query prompt:

SELECT p.name
FROM [master].sys.server_principals p, [master].sys.server_role_members m
WHERE p.principal_id = m.member_principal_id
AND m.member_principal_id <> 1
AND m.role_principal_id = 3
ORDER BY p.name

Verify with the DBA that all users listed under System Administrators are authorized DBAs and authorized to manage the database system audit configuration. Authorized application object owner accounts are Not a Finding unless they are not disabled (DG0004). If any authorized application object owner accounts are enabled, this is a Finding (for DG0116).

If this is a production environment, verify with the DBA that none of the users listed under the SYSADMIN fixed server role are application administrators.

If the BUILTIN/Administrators group is listed as a member of the SYSADMIN fixed server role, this is a Finding.

Note: Removing BUILTIN/Administrators without creating an appropriate group to administer SQL Server will result in a ‘lock out’ condition within SQL Server. Ensure the proper steps have been taken to create a new group that is added to SYSADMIN fixed server role before removing BUILTIN/Administrators. Also, ensure the SA password is known before making this change.

Fix Text (F-17955r1_fix)
Document IAO-authorized privileged role assignments in the System Security Plan. Remove assignments where not authorized. If BUILTIN\Administrators is part of the SYSADMIN fixed server role, create a custom group for SYSADMIN functions, add authorized users to the custom group, add the group to the SYSADMIN fixed server role, remove BUILTIN\Administrators from the role. If other unauthorized users exist, remove them from the role. To remove BUILTIN\Administrators from the SYSADMIN fixed server role: 1. Create a custom group for SYSADMIN functions 2. Add authorized users to the custom group 3. Add the group to the SYSADMIN fixed server role 4. Remove BUILTIN\Administrators from the role

Fix Text (F-17955r1_fix)

Document IAO-authorized privileged role assignments in the System Security Plan. Remove assignments where not authorized.

If BUILTIN\Administrators is part of the SYSADMIN fixed server role, create a custom group for SYSADMIN functions, add authorized users to the custom group, add the group to the SYSADMIN fixed server role, remove BUILTIN\Administrators from the role. If other unauthorized users exist, remove them from the role.

To remove BUILTIN\Administrators from the SYSADMIN fixed server role:

1. Create a custom group for SYSADMIN functions
2. Add authorized users to the custom group
3. Add the group to the SYSADMIN fixed server role
4. Remove BUILTIN\Administrators from the role